/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tuyensinh;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author user
 */
public class BangDiem {

    static Connection conn;
    static Statement stm;
    String sbd;
    String maNganh;
    float diemmon1;
    float diemmon2;
    float diemmon3;
    float tongdiem;

    public BangDiem() {
    }

    public BangDiem(String sbd, String maNganh, float diemmon1, float diemmon2, float diemmon3, float tongdiem) {
        this.sbd = sbd;
        this.maNganh = maNganh;
        this.diemmon1 = diemmon1;
        this.diemmon2 = diemmon2;
        this.diemmon3 = diemmon3;
        this.tongdiem = tongdiem;

    }

    public static void opendatabase() throws SQLException {
        conn = DriverManager.getConnection("jdbc:odbc:tuyensinh");
        stm = conn.createStatement();
    }

    public static void closeDatabase() throws SQLException {
        stm.close();
        conn.close();
    }

    public String getSbd() {
        return sbd;
    }

    public float getDiemmon1() {
        return diemmon1;
    }

    public float getDiemmon2() {
        return diemmon2;
    }

    public float getDiemmon3() {
        return diemmon3;
    }

    public String getMaNganh() {
        return maNganh;
    }

    public void setMaNganh(String maNganh) {
        this.maNganh = maNganh;
    }

    public float getTongdiem() {
        return tongdiem;
    }

    public void setSbd(String sobd) {
        sbd = sobd;
    }

    public void setDiemmon1(float Diem1) {
        diemmon1 = Diem1;
    }

    public void setDiemmon2(float Diem2) {
        diemmon2 = Diem2;
    }

    public void setDiemmon3(float Diem3) {
        diemmon3 = Diem3;
    }

    public void settongdiem(float Tong) {
        tongdiem = Tong;
    }

    public static BangDiem findBangDiemBySbd(String sbd) throws SQLException {
        opendatabase();
        String query = "select * from BangDiem where sbd='" + sbd + "'";
        BangDiem result = null;
        ResultSet rs = stm.executeQuery(query);
        if (rs.next()) {
            result = new BangDiem();
            result.setSbd(rs.getString("sbd"));
            result.setMaNganh(rs.getString("manganh"));
            result.setDiemmon1(rs.getFloat("diemmon1"));
            result.setDiemmon2(rs.getFloat("diemmon2"));
            result.setDiemmon3(rs.getFloat("diemmon3"));
            result.settongdiem(rs.getFloat("tongdiem"));
        }
        return result;
    }

    public static void updateDiem(String sbd, float diemmon1, float diemmon2, float diemmon3) throws SQLException {
        opendatabase();
        ThiSinh ts = ThiSinh.findThiSinhBySbd(sbd);
        float diemCong = KhuVuc.getDiemCongByMaKV(ts.getMakhuvuc());
        diemCong += DoiTuong.getDiemCongByMaDT(ts.getMadoituong());
        float tongDiem = diemmon1 + diemmon2 + diemmon3 + diemCong;
        String query = "update BangDiem set diemmon1=" + diemmon1 + ", diemmon2="
                + diemmon2 + ", diemmon3=" + diemmon3 + ", tongdiem=" + tongDiem
                + " where sbd='" + sbd + "'";
        stm.executeUpdate(query);
        closeDatabase();
    }

    public void insBangDiem() throws SQLException {
        opendatabase();
        String query = "insert into BangDiem values('" + sbd + "', '" + maNganh + "', "
                + diemmon1 + "," + diemmon2 + "," + diemmon3 + "," + tongdiem + ")";
        stm.executeUpdate(query);
        closeDatabase();
    }

    public static String getXepHang(String sbd) throws SQLException {
        BangDiem bd = BangDiem.findBangDiemBySbd(sbd);
        float tongDiem = bd.getTongdiem();
        String manganh = bd.getMaNganh();

        opendatabase();
        String result = "";
        String query = "select count(*) as tong from BangDiem where tongdiem = " + tongDiem;
        ResultSet rs = stm.executeQuery(query);
        int count = 0;
        if (rs.next()) {
            count = rs.getInt("tong");
        }
        result += "Co " + (count - 1) + " ban <b>cung truong</b> co diem thi <b>bang</b> " + tongDiem + "<br>";

        query = "select count(*) as tong from BangDiem where tongdiem > " + tongDiem;
        rs = stm.executeQuery(query);
        if (rs.next()) {
            count = rs.getInt("tong");
        }
        result += "Co " + count + " ban <b>cung truong</b> co diem thi <b>lon hon</b> " + tongDiem + "<br>";

        query = "select count(*) as tong from BangDiem where tongdiem = " + tongDiem + " and manganh='" + manganh + "'";;
        rs = stm.executeQuery(query);
        if (rs.next()) {
            count = rs.getInt("tong");
        }
        result += "Co " + (count - 1) + " ban <b>cung nganh</b> co diem thi <b>bang</b> " + tongDiem + "<br>";

        query = "select count(*) as tong from BangDiem where tongdiem > " + tongDiem + " and manganh='" + manganh + "'";
        rs = stm.executeQuery(query);
        if (rs.next()) {
            count = rs.getInt("tong");
        }
        result += "Co " + count + " ban <b>cung nganh</b> co diem thi <b>lon hon</b> " + tongDiem + "<br>";

        return result;
    }
}
